# import datetime
# import json
# import math
# import os
# import sys
# import time
#
# # 将工程目录加入包扫描
# currPath = os.path.abspath(os.path.dirname(__file__))
# srcPath = os.path.split(currPath)[0]
# rootPath = os.path.split(srcPath)[0]
# projectPath = os.path.split(rootPath)[0]
# sys.path.append(projectPath)
#
# from itertools import groupby
# from operator import itemgetter
# from tqdm import tqdm
# from src.db import AbsSqlServer
# from src.db.AbsSqlServer import AbsSqlServerDev
# from src.tasks.regionSpread.CustomJsonEncoder import toJson
# from src.utils import BatchUtils, CommonUtils
# from src.db.CcxDataCenterDb import CcxDataCenter
# from src.db.ProdDb import ProdDb
# from src.utils.LogUtils import Logger
# from src.db.FCDB import FCDB
#
# logger = Logger()
#
#
# class RegionSpread:
#     def __init__(self, logger, localDb, dataCenterDb, absSqlServer):
#         self.logger = logger
#         self.localDb = localDb
#         self.dataCenterDb = dataCenterDb
#         self.arg = sys.argv
#         self.fcdb = FCDB(logSql=False, autocommit=True)
#         self.absSqlServer = absSqlServer
#         # self.cursor = AbsSqlServer.Conn.conn.cursor()
#         print()
#
#     def queryRegions(self):
#         sql = """
#                        SELECT * from regions
#                     where release_status =1 and release_status =1 and ver=0
#         """
#         query = self.localDb.Query(sql)
#         dataMap = {
#
#         }
#         for item in query:
#             dataMap.update({
#                 item['code']: item
#             })
#         return dataMap
#
#     def queryPlatformEntList(self):
#         sql = """
#             SELECT
#         a.*,
#         a.credit_code,
#         d.NAME,
#         REPLACE ( b.VALUE , '.0', '' ) AS regionCode,
#         e.value AS isLead
#         FROM
#         enterprise_info_custom d
#         left JOIN city_investment_companies a ON d.credit_code = a.credit_code
#         left JOIN city_investment_companies_data_detail b ON a.id = b.foreign_id
#         and b.second_code='CTJC002001'
#         left JOIN city_investment_companies_data_detail e ON a.id = e.foreign_id
#         and e.second_code='CTJC002003'
#         left JOIN city_investment_company_targets c ON b.second_code = c.second_code
#         WHERE
#         a.ver = 0
#         AND a.release_status = 1
#         AND a.`status` = 1
#         AND d.release_status = 1
#         and a.ver=0
#         AND d.ver = 0
#         AND d.type_code = 'T002'
#         AND d.audit_status = 1
#
#         GROUP BY
#         a.credit_code
#         """
#         return self.dataCenterDb.Query(sql)
#
#     # 获取所有债券数据
#     def getAllBonds(self):
#         sql = """
#                 SELECT
#                     SECODE,
#                     BONDNAME,
#                     BONDSNAME,
#                     EXCHANGE,
#                     b.credit_code,
#                     b.enterprise_name,
#                     a.ISGUARANTEE,
#                     a.MATURITYYEAR,
#                     a.ACTISSAMT,
#                     a.RAISEMODE,
#                     a.CVTBDEXPIREMEMP,
#                     a.NEWRATE,
#                     b.platform_region_code,
#                     a.ISSUECOMPCODE
#                 FROM
#                     tq_bd_newestbasicinfo a
#                     left join ent_enterprise b on b.comp_code =a.ISSUECOMPCODE
#                     where b.ent_type ='T002'
#         """
#         return self.localDb.Query(sql)
#
#     # 查询财汇收益率数据
#     def getYIELDTable(self, seCode, date, endDate):
#         sql = """
#             select TRADEDATE,SECODE,EXCHANGE,VALUATIONTYPE,TERMTOMATURITY,YIELD from TQ_QT_CBESTIMATE
#             where SECODE=%s
#             and DATASOURCE=1
#             and TRADEDATE >= %s
#         """
#         if endDate is not None:
#             sql += " and TRADEDATE<=" + endDate
#         return self.fcdb.Query(sql, (seCode, date))
#
#     def getYieldcurve(self, startDate):
#         sql = """
#             select
#             tqy.YCURVECODE,
#                            tqy.TRADEDATE,
#                            tqy.YCURVETYPE,
#                            tqy.MATURITY,
#                            tqy.YIELD
#
#             from TQ_QT_YIELDCURVE tqy
#             where TRADEDATE =%s and tqy.MATURITY >0 and tqy.YCURVETYPE=1 and tqy.YCURVECODE=269
#             order BY tqy.MATURITY
#         """
#         content = None
#         filePath = "E:\Fits2.0\\files\\" + startDate + ".txt"
#         try:
#             with open(filePath, "r") as file:
#                 content = file.read()
#             if content is not None:
#                 return json.loads(content)
#         except FileNotFoundError:
#             pass
#
#         query = self.fcdb.Query(sql, startDate)
#         self.saveFile(query, startDate + '.txt')
#         return query
#
#     def saveFile(self, data, fileName):
#         with open("E:\Fits2.0\\files\\" + fileName, 'w') as f:  # 如果filename不存在会自动创建， 'w'表示写数据，写之前会清空文件中的原有数据！
#             f.write(toJson(data))
#
#     def getSeCode(self, itemByBondName: list):
#         # 只有一条，直接取
#         if len(itemByBondName) == 1:
#             return itemByBondName[0]
#         else:
#             dataMap = {
#
#             }
#             for item in itemByBondName:
#                 dataMap.update({
#                     item['EXCHANGE']: item
#                 })
#             if dataMap.__contains__('001005'):
#                 return dataMap.get("001005")
#
#             if dataMap.__contains__('001002'):
#                 return dataMap.get("001002")
#
#             if dataMap.__contains__('001003'):
#                 return dataMap.get("001003")
#
#             if dataMap.__contains__('001006'):
#                 return dataMap.get("001006")
#             if dataMap.__contains__('001018'):
#                 return dataMap.get("001018")
#             return None
#
#     def __updateLocal__(self, updateDatas):
#         sql = """
#                     INSERT INTO Fits2Test.dbo.base_bonds_interest_rate_spread
#                      (secode, bond_name, curve_yield, comp_code, trade_date, cbestimate_yield, interest_rate_spread,
#                       is_guarantee, maturity_year, actissamt, raise_mode, cvtbdexpirememp, new_rate, platform_region_code
#                       , province_code, city_code, county_code, [type], [level], functional_code,termtomaturity,credit_code,short_name)
#                     VALUES(%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
#         """
#         # return self.localDb.ExecMany(sql, updateDatas)
#         many = self.absSqlServer.execute_many(sql, updateDatas)
#         return many
#
#     def deleteDataByDate(self, date, endDate):
#         sql = """
#          DELETE FROM Fits2Test.dbo.base_bonds_interest_rate_spread WHERE  trade_date >= %s
#         """
#         if endDate is not None:
#             sql += " and trade_date<=" + endDate
#         if date is not None:
#             many = self.absSqlServer.execute_non_query(sql, date)
#             return many
#
#     def __updatePlatformBondsList__(self, updateDatas):
#         sql = """
#                     INSERT INTO base_bonds_list_platform
#                     ( secode, bond_name, comp_code, is_guarantee, maturity_year, actissamt, raise_mode, cvtbdexpirememp, new_rate,
#                      platform_region_code, province_code, city_code, county_code, `type`, `level`, functional_code) VALUES
#                      ( %s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);
#         """
#         return self.localDb.ExecMany(sql, updateDatas)
#
#     def saveData(self, updateData: list, dbMainResultList: list):
#         if updateData is None and len(updateData) == 0:
#             return
#         batchList = []
#         for r in updateData:
#             batchList.append((
#                 r.get("secode"),
#                 r.get("bondName"),
#                 r.get("curve_yield"),
#                 r.get("compCode"),
#                 r.get("tradeDate"),
#                 r.get("cbestimateYield"),
#                 r.get("interest_rate_spread"),
#                 r.get("ISGUARANTEE"),
#                 r.get("MATURITYYEAR"),
#                 r.get("ACTISSAMT"),
#                 r.get("RAISEMODE"),
#                 r.get("CVTBDEXPIREMEMP"),
#                 r.get("NEWRATE"),
#                 r.get("platform_region_code"),
#                 r.get("province_code"),
#                 r.get("city_code"),
#                 r.get("county_code"),
#                 r.get("type"),
#                 r.get("level"),
#                 r.get("functional_code"),
#                 r.get("termtomaturity"),
#                 r.get("credit_code"),
#                 r.get("shortName"),
#             ))
#         updatedCount = BatchUtils.batchInsert(self.__updateLocal__, batchList, 20000)
#         # 更新主表
#         # batchMainList = []
#         # for r in dbMainResultList:
#         #     batchMainList.append((
#         #         r.get("secode"),
#         #         r.get("bondName"),
#         #         r.get("compCode"),
#         #         r.get("ISGUARANTEE"),
#         #         r.get("MATURITYYEAR"),
#         #         r.get("ACTISSAMT"),
#         #         r.get("RAISEMODE"),
#         #         r.get("CVTBDEXPIREMEMP"),
#         #         r.get("NEWRATE"),
#         #         r.get("platform_region_code"),
#         #         r.get("province_code"),
#         #         r.get("city_code"),
#         #         r.get("county_code"),
#         #         r.get("type"),
#         #         r.get("level"),
#         #         r.get("functional_code"),
#         #     ))
#         # BatchUtils.batchInsert(self.__updatePlatformBondsList__, batchMainList)
#         # print("保存数据：{}{}".format(str(updatedCount), "条"))
#         return
#
#         # 一、利差计算
#
#     # 1.选取所有城投企业债券
#     # 2.根据债券全称去重，EXCHANGE优先银行间(001005)，其次交易所(001002或001003)，再次固定收益平台(001006或001018)，最后其他
#     # 3.选择收益率YIELD(TQ_QT_CBESTIMATE),优先选择VALUATIONTYPE=1，如果没有则选择VALUATIONTYPE=2，如果VALUATIONTYPE=1或2时有两条数据则任选一条；并且DATASOURCE=1
#     # 4.选取债券剩余年限(TERMTOMATURITY)、国开债收益率曲线剩余期限(MATURITY),线性插值计算YIELD(TQ_QT_YIELDCURVE,YCURVETYPE=1)
#     # 5.债券利差计算   YIELD(TQ_QT_CBESTIMATE)-YIELD(TQ_QT_YIELDCURVE,YCURVETYPE=1)
#     # 6.城投/区域利差计算——中位数或发行额加权平均:实际发行额ACTISSAMT(TQ_BD_NEWESTBASICINFO)
#     def process(self):
#         dateByCommand = CommonUtils.isCommandParam(self.arg, "date")
#         endDateByCommand = CommonUtils.isCommandParam(self.arg, "endDate")
#         # 判断是否有指定日期参数
#         # 开始交易日
#         if dateByCommand['exist']:
#             date = dateByCommand["value"]
#         else:
#             date = datetime.date.today().replace("-", "")
#
#         # 参数
#         # 结束交易日
#         endDate = None
#         if endDateByCommand['exist']:
#             endDate = endDateByCommand["value"]
#
#         self.deleteDataByDate(date, endDate)
#         # 获取全部债券
#         bondsAll = self.getAllBonds()
#         # 查询全部区域
#         regionsAll = dict(self.queryRegions())
#         # 根据债券全称分组，判断exchange
#         # 排序
#         bondsAll.sort(key=itemgetter("BONDNAME"))
#
#         # 对债券全程字段分组汇总
#         bondsAll = groupby(bondsAll, key=itemgetter("BONDNAME"))
#         for bondName, items in bondsAll:
#             # 需要保存的数据
#             dbResultList = []
#             itemByBondName = list(items)
#             # 获取唯一secode
#             bondsInfo = self.getSeCode(itemByBondName)
#             if bondsInfo is None:
#                 continue
#             seCode = bondsInfo['SECODE']
#             credit_code = bondsInfo['credit_code']
#             shortName = bondsInfo['BONDSNAME']
#             if seCode is None:
#                 continue
#             # SECODE拿到后抽取收益率数据
#             yield_table = self.getYIELDTable(seCode, date, endDate)
#             # 收益率数据缺失，跳过
#             if len(yield_table) == 0:
#                 # tqdm.write(bondName + "未查询到收益率数据，跳过。")
#                 continue
#             # 所属城投区域CODE
#             platform_region_code = bondsInfo['platform_region_code']
#             for yield_item in yield_table:
#                 # 交易日
#                 tradeDate = yield_item['TRADEDATE']
#                 # 剩余期限
#                 termtomaturity = yield_item['TERMTOMATURITY']
#                 yieldData = yield_item['YIELD']
#                 yieldcurveList = self.getYieldcurve(tradeDate)
#                 if len(yieldcurveList) == 0:
#                     # tqdm.write(bondName + "收益率曲线表缺失，跳过")
#                     continue
#                 spread = self.calSpread(termtomaturity, yieldData, yieldcurveList)
#                 dbResult = {
#                     "compCode": bondsInfo['ISSUECOMPCODE'],
#                     "curve_yield": spread,
#                     "cbestimateYield": yieldData,
#                     "interest_rate_spread": None if spread is None else float(yieldData) - float(spread),
#                     "tradeDate": tradeDate,
#                     "secode": seCode,
#                     "bondName": bondName,
#                     "ISGUARANTEE": bondsInfo['ISGUARANTEE'],
#                     "termtomaturity": termtomaturity,
#                     "MATURITYYEAR": bondsInfo['MATURITYYEAR'],
#                     "ACTISSAMT": bondsInfo['ACTISSAMT'],
#                     "RAISEMODE": bondsInfo['RAISEMODE'],
#                     "CVTBDEXPIREMEMP": bondsInfo['CVTBDEXPIREMEMP'],
#                     "NEWRATE": bondsInfo['NEWRATE'],
#                     "platform_region_code": platform_region_code,
#                     "province_code": regionsAll.get(platform_region_code)['province_code'],
#                     "city_code": regionsAll.get(platform_region_code)['city_code'],
#                     "county_code": regionsAll.get(platform_region_code)['county_code'],
#                     "type": regionsAll.get(platform_region_code)['type'],
#                     "level": regionsAll.get(platform_region_code)['level'],
#                     "credit_code": credit_code,
#                     "shortName": shortName
#                 }
#                 dbResultList.append(dbResult)
#             self.saveData(dbResultList, None)
#
#     # 计算力差
#     def calSpread(self, termtomaturity, yieldData, yieldcurveList):
#         for index, curveItem in enumerate(yieldcurveList):
#             maturity_ = float(curveItem['MATURITY'])
#             yield_ = float(curveItem['YIELD'])
#             if index == len(yieldcurveList) - 1:
#                 break
#             if termtomaturity == maturity_:
#                 return float(yieldData) - float(yield_)
#
#             if (termtomaturity > maturity_) and (termtomaturity < yieldcurveList[index + 1]['MATURITY']):
#                 T1MATURITY = float(yieldcurveList[index + 1]['MATURITY'])
#                 T1YIELD = float(yieldcurveList[index + 1]['YIELD'])
#                 # 2.540105
#                 # Y=Y1+ (Y2-Y1)× (X-X1)/ (X2-X1
#                 # 收益率1 = yield_ + (T1YIELD - yield_) * (yieldData - maturity_) / (T1MATURITY - maturity_)
#                 收益率 = (float(termtomaturity) - maturity_) / (T1MATURITY - maturity_) * (T1YIELD - yield_) + yield_
#                 return 收益率
#         return None
#
#
# # python RegionSpread.py --date=20200101 --endDate=20201231
# # python RegionSpread.py --date=20210101 --endDate=20211231
# # python RegionSpread.py --date=20220101 --endDate=20221231 running
# # python RegionSpread.py --date=20230101 --endDate=20231231 running
# if __name__ == "__main__":
#     logger = Logger()
#     localDb = ProdDb(logSql=False, autocommit=True)
#     dataCenterDb = CcxDataCenter(logSql=False, autocommit=True)
#     absSqlServer = AbsSqlServerDev()
#     t = time.perf_counter()
#     logger.info("========计算区域利差数据 开始 ========")
#     task = RegionSpread(logger=logger, localDb=localDb, dataCenterDb=dataCenterDb, absSqlServer=absSqlServer)
#     task.process()
#
#     cast = (time.perf_counter() - t)
#     m = math.floor(cast / 60)
#     s = cast - m * 60
#     logger.info('总耗时: %d分%.3f秒' % (m, s))
#     logger.info("========计算区域利差数据 结束 ========")
